"""
Case Type   : 功能测试
Case Name   : where子句指定(+)连接外表,合理报错
Create At   : 2022/11/22
Owner       : @zou_jialiang0501208402
Description :
            1、修改增量检查点为off
            2、重启数据库生效
            3、创建普通表1外表2,在表中插入数据
            4、where子句指定(+)连接外表查询数据并将结果插入到表
            5、清理环境
Expect      :
            1、成功
            2、成功
            3、成功
            4、失败
            5、成功
History     :
    modified by nanyang12 2025/3/18: 研发代码变更，增加motserver开关配置
"""

import os
import unittest
from yat.test import Node
from yat.test import macro

from testcase.utils.Logger import Logger
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant


class SQL(unittest.TestCase):

    def setUp(self):
        self.log = Logger()
        self.log.info(f'-----{os.path.basename(__file__)} start-----')
        self.primary_sh = CommonSH('PrimaryDbUser')
        self.Constant = Constant()
        self.pri_node = Node('PrimaryDbUser')
        self.env_path = macro.DB_ENV_PATH
        self.t_name1 = 't_select_into_0066_1'
        self.t_name2 = 't_select_into_0066_2'
        self.t_name3 = 't_select_into_0066_3'

    def test_where(self):
        step1 = '--step1:修改增量检查点为off;expect:成功--'
        self.log.info(step1)
        sql_cmd = f'''
                    alter system set enable_incremental_checkpoint to off;
                  '''
        result = self.primary_sh.execut_db_sql(sql_cmd)
        self.log.info(result)
        self.assertIn(self.Constant.alter_system_success_msg, result,
                      '执行失败' + step1)
        self.log.info('------开启enable_mot_server参数-----')
        res = self.primary_sh.execute_gsguc(
            'set', self.Constant.GSGUC_SUCCESS_MSG,
            f'enable_mot_server=on')
        self.assertTrue(res)

        step2 = '--step2:重启数据库生效;expect:成功--'
        self.log.info(step2)
        restart_msg = self.primary_sh.restart_db_cluster()
        self.assertTrue(restart_msg, '执行失败' + step2)

        step3 = '--step3:创建普通表1外表2,在表中插入数据;expect:成功--'
        self.log.info(step3)
        sql_cmd = f"drop table if exists {self.t_name1};" \
                  f"create table t_select_into_0066_1 (" \
                  f"id int,c_clob varchar(10));" \
                  f"drop foreign table if exists {self.t_name2};" \
                  f"create foreign table t_select_into_0066_2 (" \
                  f"id int,c_clob1 varchar(10)) " \
                  f"options (encoding 'utf-8');" \
                  f"insert into {self.t_name1} values(1,'h');" \
                  f"insert into {self.t_name1} values(2,'G');" \
                  f"insert into {self.t_name1} values(3,'f');" \
                  f"insert into {self.t_name1} values(4,'E');" \
                  f"insert into {self.t_name2} values(1,'h');" \
                  f"insert into {self.t_name2} values(2,'G');" \
                  f"insert into {self.t_name2} values(7,'b');" \
                  f"insert into {self.t_name2} values(8,'a');"
        result = self.primary_sh.execut_db_sql(sql_cmd)
        self.log.info(result)
        self.assertIn(self.Constant.TABLE_DROP_SUCCESS, result,
                      '执行失败' + step3)
        self.assertIn(self.Constant.TABLE_CREATE_SUCCESS, result,
                      '执行失败' + step3)
        self.assertIn(self.Constant.DROP_FOREIGN_SUCCESS_MSG, result,
                      '执行失败' + step3)
        self.assertIn(self.Constant.CREATE_FOREIGN_SUCCESS_MSG, result,
                      '执行失败' + step3)
        self.assertEqual(8, result.count(self.Constant.INSERT_SUCCESS_MSG),
                         '执行失败' + step3)

        step4 = '--step4:where子句指定(+)连接外表查询' \
                '数据并将结果插入到表;expect:失败--'
        self.log.info(step4)
        sql_cmd = f"select c_clob,c_clob1 into {self.t_name3} " \
                  f"from {self.t_name1},{self.t_name2} " \
                  f"where {self.t_name1}.id = {self.t_name2}.id(+);"
        result = self.primary_sh.execut_db_sql(sql_cmd)
        self.log.info(result)
        self.assertIn(self.Constant.create_table_error_msg, result,
                      '执行失败' + step4)

    def tearDown(self):
        step5 = '--step5:清理环境;expect:成功--'
        self.log.info(step5)
        sql_cmd = f'''
                    drop table if exists {self.t_name1};
                    drop foreign table if exists {self.t_name2};
                    alter system set enable_incremental_checkpoint to on;
                    CHECKPOINT;
                    '''
        result = self.primary_sh.execut_db_sql(sql_cmd)
        self.log.info(result)
        self.assertIn(self.Constant.TABLE_DROP_SUCCESS, result,
                      '执行失败' + step5)
        self.assertIn(self.Constant.DROP_FOREIGN_SUCCESS_MSG, result,
                      '执行失败' + step5)
        self.assertIn(self.Constant.alter_system_success_msg, result,
                      '执行失败' + step5)
        self.log.info('------关闭enable_mot_server参数-----')
        res = self.primary_sh.execute_gsguc(
            'set', self.Constant.GSGUC_SUCCESS_MSG,
            f'enable_mot_server=off')
        self.assertTrue(res)
        restart_msg = self.primary_sh.restart_db_cluster()
        self.assertTrue(restart_msg, '执行失败' + step5)
        self.log.info(f'-----{os.path.basename(__file__)} end-----')